import os
import pandas as pd
import numpy as np
import tools

pd.set_option('display.unicode.east_asian_width', True)


def get_score(path):
    filenames = tools.get_filenames(path)
    tools.检查路径名(filenames)
    ids = []
    names = []
    scores = []
    for filename in filenames:
        if len(filename.split('.')) < 3:
            print(filename)
            exit(0)

        id = filename.split('.')[0].strip('-').split('-')[1]
        name = filename.split('.')[0].strip('-').split('-')[2]
        score = filename.split('.')[2]
        # print(f'{id} {name} {score}')
        ids.append(id)
        # names.append(name)
        scores.append(score)

    df_new = pd.DataFrame({'学号': ids, path.split('\\')[-1]: scores})
    df_new.set_index('学号', inplace=True)

    return df_new


base_path = r"D:\100-Project\2024-2025-1学期安排\武昌首义学院\24101308_《通信数据分析技术（企业）》\作业和报告"
docx_dir = base_path + r"\提交"
excel_file = base_path + r"\成绩提取.xlsx"

df = pd.read_excel(excel_file, header=0)
df.fillna(0, inplace=True)
df = df.iloc[:, 0:2]
df = df.astype(str)
df = df.set_index('学号')

dirs = [r"\上机报告1", r"\上机报告2", r"\上机报告3", r"\上机报告4",
        r"\上机报告5", r"\上机报告6", r"\上机报告7", r"\上机报告8",
        r"\作业1", r"\作业2", r"\作业3", r"\作业4", r"\作业5", r"\作业6"
        ]

for dir in dirs:
    df1 = get_score(base_path + dir)
    df = pd.merge(df, df1, left_index=True, right_index=True, how='outer')

df.fillna(0, inplace=True)
df.to_excel(excel_file, index=True)
